package com.itheima.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.itheima.domain.Book;
import com.itheima.utils.JDBCUtils;

/**
 * 数据库表t_book增删查改DAO层操作
 * @author dell-pc
 *
 */
public class BookDao {
	
	/**
	 * 查询所有的书籍信息
	 * @return
	 * @throws SQLException 
	 */
	public List<Book> findAllBook() throws SQLException{
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from t_book";
		List<Book> listBook = qr.query(sql, new BeanListHandler<Book>(Book.class));
		return listBook;
	}

	/**
	 * 查询某个id的书籍信息
	 * @param bid
	 * @return
	 * @throws SQLException 
	 */
	public Book findBookById(Integer bid) throws SQLException {
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from t_book where bid = ?";
		Book book = qr.query(sql, new BeanHandler<Book>(Book.class),bid);
		return book;
	}

	/**
	 * 更新书籍操作
	 * @param book
	 * @return
	 * @throws SQLException 
	 */
	public int update(Book book) throws SQLException {
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update t_book set bookname = ?,author=?,price=?,b_desc=? where bid = ?";
		Object[] params = {book.getBookName(),book.getAuthor(),book.getPrice(),book.getB_desc(),book.getBid()};
		int i = qr.update(sql, params);
		return i;
	}
	
	/**
	 * 添加操作
	 * @param book
	 * @return
	 * @throws SQLException 
	 */
	public int addBook(Book book) throws SQLException{
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		//编写sql
		String sql = "insert into t_book values(null,?,?,?,?)";
		//设置参数
		Object params[] = {book.getBookName(),book.getAuthor(),book.getPrice(),book.getB_desc()};
		//执行sql语句，并返回影响行数
		int i = qr.update(sql, params);
		return i;
	}
	
	/**
	 * 删除操作
	 * @param bid
	 * @return
	 * @throws SQLException 
	 */
	public int deleteBook(Integer bid) throws SQLException{
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		//编写sql
		String sql = "delete from t_book where bid=?";	
		//执行sql语句，并返回影响行数
		int i = qr.update(sql, bid);
		return i;
	}
	
	/**
	 * 事务控制批量删除操作
	 * @param bid
	 * @return
	 * @throws SQLException 
	 */
	public int deleteBook(Connection conn , Integer bid) throws SQLException{
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		//编写sql
		String sql = "delete from t_book where bid=?";	
		//执行sql语句，并返回影响行数
		int i = qr.update(sql, bid);
		return i;
	}

	/**
	 * 根据书籍名称 价格区间查询
	 * @param bookName
	 * @param s_price
	 * @param e_price
	 * @return
	 * @throws SQLException
	 */
	public List<Book> search(String bookName, String s_price, String e_price) throws SQLException {
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		//String sql = "select * from t_book where 1=1 and bookName = ? and (price >= ?  and price <= ?)";
		String sql = "select * from t_book where 1=1";
		List<String> paramList = new ArrayList<String>();
		if(bookName != null && !bookName.equals("")){
			sql += " and bookname like ? ";
			paramList.add("%"+bookName+"%");
		}
		if(s_price != null && !s_price.equals("")){
			sql += " and price >= ? ";
			paramList.add(s_price);
		}
		if(e_price != null && !e_price.equals("")){
			sql += " and price <= ? ";
			paramList.add(e_price);
		}
		System.out.println(sql);		
		Object[] params = paramList.toArray();
		System.out.println(params);
		List<Book> bookList = qr.query(sql, new BeanListHandler<Book>(Book.class), params);
		return bookList;
	}

	/**
	 * 查询t_book所有记录的总数
	 * @return
	 * @throws SQLException 
	 */
	public int findCount(String bookName, String s_price, String e_price) throws SQLException {
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from t_book where 1=1";
		List<String> paramList = new ArrayList<String>();
		if(bookName != null && !bookName.equals("")){
			sql += " and bookname like ? ";
			paramList.add("%"+bookName+"%");
		}
		if(s_price != null && !s_price.equals("")){
			sql += " and price >= ? ";
			paramList.add(s_price);
		}
		if(e_price != null && !e_price.equals("")){
			sql += " and price <= ? ";
			paramList.add(e_price);
		}
		System.out.println(sql);		
		Object[] params = paramList.toArray();
		Long count = (Long) qr.query(sql, new ScalarHandler(),params);
		return count.intValue();
	}

	/**
	 * 分页查询t_book的数据
	 * @param begin
	 * @param pageSize
	 * @return
	 * @throws SQLException 
	 */
	public List<Book> findByPage(int begin, int pageSize,String bookName, String s_price, String e_price) throws SQLException {
		QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from t_book where 1=1";
		List<Object> paramList = new ArrayList<Object>();
		
		if(bookName != null && !bookName.equals("")){
			sql += " and bookname like ? ";
			paramList.add("%"+bookName+"%");
		}
		if(s_price != null && !s_price.equals("")){
			sql += " and price >= ? ";
			paramList.add(s_price);
		}
		if(e_price != null && !e_price.equals("")){
			sql += " and price <= ? ";
			paramList.add(e_price);
		}
		sql += " limit ?,?";
		paramList.add(begin);
		paramList.add(pageSize);
		System.out.println(sql);		
		Object[] params = paramList.toArray();
		List<Book> bookList = qr.query(sql, new BeanListHandler<Book>(Book.class), params);
		return bookList;
	}
}
